

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBUtils {
    
    public final static int SQL_INSERT = 1;
    public final static int SQL_UPDATE = 2;
    public final static int SQL_DELETE = 3;

    private String table = "";
    private List columns = new ArrayList(0);
    private List types = new ArrayList(0);
    private List objects = new ArrayList(0);
    private List whereColumns = new ArrayList(0);
    private List whereTypes = new ArrayList(0);
    private List whereObjects = new ArrayList(0);
    
    public DBUtils() {
        
    }
    
    public void setTable(String table) {
        this.table = table;
    }
    
    public void add(String column, Object object, int type) {
        this.columns.add(column);
        this.objects.add(object);
        this.types.add(new Integer(type));
    }
    
    public void addWhere(String column, Object object, int type) {
        this.whereColumns.add(column);
        this.whereObjects.add(object);
        this.whereTypes.add(new Integer(type));
    }
    
    public void clear() {
        this.table = "";
        this.columns.clear();
        this.objects.clear();
        this.types.clear();
        this.whereColumns.clear();
        this.whereObjects.clear();
        this.whereTypes.clear();
    }
    
    public int execute(Connection c, int type) throws SQLException {
        int i = -1;
        switch (type) {
            case SQL_INSERT:
                i = this.executeInsert(c);
                break;
            case SQL_UPDATE:
                i = this.executeUpdate(c);
                break;
            case SQL_DELETE:
                i = this.executeDelete(c);
                break;
        }
        return i;
    }
    
    private int executeInsert(Connection c) throws SQLException {
        int ret = -1;
        String sql = this.getSql(SQL_INSERT);
        System.out.println(sql);
        if (null != c) {
            PreparedStatement ps = null;
            try {
                ps = c.prepareStatement(sql);
                for (int i = 0; i < this.columns.size(); i++) {
                    ps.setObject(i + 1, this.objects.get(i), ((Integer) this.types.get(i)).intValue());
                }
                ret = ps.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                throw e;
            } finally {
                close(ps);
            }
        }
        return ret;
    }
    
    private int executeUpdate(Connection c) throws SQLException {
        int ret = -1;
        String sql = this.getSql(SQL_UPDATE);
        System.out.println(sql);
        if (null != c) {
            PreparedStatement ps = null;
            try {
                ps = c.prepareStatement(sql);
                for (int i = 0; i < this.columns.size(); i++) {
                    ps.setObject(i + 1, this.objects.get(i), ((Integer) this.types.get(i)).intValue());
                }
                for (int i = 0; i < this.whereColumns.size(); i++) {
                    ps.setObject(i + this.columns.size() + 1, this.whereObjects.get(i),
                            ((Integer) this.whereTypes.get(i)).intValue());
                }
                ret = ps.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                throw e;
            } finally {
                close(ps);
            }
        }
        return ret;
    }
    
    private int executeDelete(Connection c) throws SQLException {
        int ret = -1;
        String sql = this.getSql(SQL_DELETE);
        System.out.println(sql);
        if (null != c) {
            PreparedStatement ps = null;
            try {
                ps = c.prepareStatement(sql);
                for (int i = 0; i < this.whereColumns.size(); i++) {
                    ps.setObject(i + 1, this.whereObjects.get(i),
                            ((Integer) this.whereTypes.get(i)).intValue());
                }
                ret = ps.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                throw e;
            } finally {
                close(ps);
            }
        }
        return ret;
    }
    
    public String getSql(int type) {
        String sql = null;
        switch (type) {
            case SQL_INSERT:
                sql = this.getSqlInsert();
                break;
            case SQL_UPDATE:
                sql = this.getSqlUpdate();
                break;
            case SQL_DELETE:
                sql = this.getSqlDelete();
                break;
        }
        return sql;
    }
    
    private String getSqlInsert() {
        String[] params = new String[this.columns.size()];
        Arrays.fill(params, "?");
        
        String sql = "INSERT INTO #TABLE# (#COLUMNS#) VALUES (#PARAMS#)";
        sql = sql.replaceFirst("#TABLE#", this.table);
        sql = sql.replaceFirst("#COLUMNS#", this.columns.toString().replaceAll("[\\[\\]]", ""));
        sql = sql.replaceFirst("#PARAMS#", Arrays.asList(params).toString().replaceAll("[\\[\\]]", ""));
        return sql;
    }
    
    private String getSqlUpdate() {
        List params = new ArrayList(this.columns.size());
        for (int i = 0; i < this.columns.size(); i++) {
            params.add(this.columns.get(i) + " = ?");
        }
        StringBuffer where = new StringBuffer();
        for (int i = 0; i < this.whereColumns.size(); i++) {
            where.append(" AND " + this.whereColumns.get(i) + " = ?");
        }
        
        String sql = "UPDATE #TABLE# SET #PARAMS# WHERE 1 = 1 #WHERE#";
        sql = sql.replaceAll("#TABLE#", this.table);
        sql = sql.replaceFirst("#PARAMS#", params.toString().replaceAll("[\\[\\]]", ""));
        sql = sql.replaceFirst("#WHERE#", where.toString());
        return sql;
    }
    
    private String getSqlDelete() {
        StringBuffer where = new StringBuffer();
        for (int i = 0; i < this.whereColumns.size(); i++) {
            where.append(" AND " + this.whereColumns.get(i) + " = ?");
        }
        
        String sql = "DELETE FROM #TABLE# WHERE 1 = 1 #WHERE#";
        sql = sql.replaceAll("#TABLE#", this.table);
        sql = sql.replaceFirst("#WHERE#", where.toString());
        return sql;
    }
    
    /**
     * @return List<Map<String, Object>>
     */
    public static List executeQuery(Connection c, String sql) {
        List list = null;
        Statement s = null;
        ResultSet r = null;
        try {
            s = c.createStatement();
            r = s.executeQuery(sql);
            ResultSetMetaData md = r.getMetaData();
            int i = md.getColumnCount();
            while (r.next()) {
                if (null == list) {
                    list = new ArrayList(0);
                }
                Map m = new HashMap(0);
                for (int j = 0; j < i; j++) {
                    m.put(md.getColumnLabel(j + 1), r.getObject(j + 1));
                }
                list.add(m);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(r);
            close(s);
        }
        return list;
    }
    
    public static boolean exist(Connection c, String sql) {
        boolean b = false;
        Statement s = null;
        ResultSet r = null;
        try {
            s = c.createStatement();
            r = s.executeQuery(sql);
            if (r.next()) {
                b = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(r);
            close(s);
        }
        return b;
    }
    
    public static void close(Connection c) {
        if (null != c) {
            try {
                c.close();
            } catch (Exception e) {}
        }
    }
    public static void close(Statement s) {
        if (null != s) {
            try {
                s.close();
            } catch (Exception e) {}
        }
    }
    public static void close(ResultSet r) {
        if (null != r) {
            try {
                r.close();
            } catch (Exception e) {}
        }
    }
    
    public static void rollback(Connection c) {
        if (null != c) {
            try {
                c.rollback();
            } catch (Exception e) {}
        }
    }
    
    public static void main(String[] args) {
        DBUtils dbu = new DBUtils();
        dbu.setTable("U_GFGL.T_GFGL_XXXX");
        dbu.add("A", "", Types.VARCHAR);
        dbu.add("B", "", Types.VARCHAR);
        dbu.add("C", "", Types.VARCHAR);
        dbu.add("D", "", Types.VARCHAR);
        dbu.add("E", "", Types.VARCHAR);
        dbu.add("F", "", Types.VARCHAR);
        /*dbu.addWhere("AF", "", Types.VARCHAR);
        dbu.addWhere("BF", "", Types.VARCHAR);
        dbu.addWhere("CF", "", Types.VARCHAR);
        dbu.addWhere("DF", "", Types.VARCHAR);*/
        
        System.out.println(dbu.getSql(DBUtils.SQL_INSERT));
        System.out.println(dbu.getSql(DBUtils.SQL_UPDATE));
        System.out.println(dbu.getSql(DBUtils.SQL_DELETE));
        
        dbu.clear();
        System.out.println(dbu.getSql(DBUtils.SQL_INSERT));
        System.out.println(dbu.getSql(DBUtils.SQL_UPDATE));
        System.out.println(dbu.getSql(DBUtils.SQL_DELETE));
        
    }
}
